package com.aaa.qy127.dao.impl;

import com.aaa.qy127.dao.TongJiDao;
import com.aaa.qy127.util.JDBCUtil;

import java.util.List;
import java.util.Map;

public class TongJiDaoImpl implements TongJiDao {
    /**
     * 查询库存书籍类型和数量
     * @return
     */
    @Override
    public List<Map<String, Object>> gettype() {
        String sql = "select sum(count) value,typename from t_book b JOIN t_type t on t.typeid = b.typeid GROUP BY b.typeid";
        List<Map<String, Object>> list = JDBCUtil.queryMap(sql);
        if (list.size()>0){
            return list;
        }else {
            return null;
        }
    }
    /**
     * 查询库存书籍名称和数量
     * @return
     */
    @Override
    public List<Map<String, Object>> getbook() {
        String sql = "select bookname, count from t_book";
        List<Map<String, Object>> list = JDBCUtil.queryMap(sql);
        if (list.size()>0){
            return list;
        }else {
            return null;
        }
    }
    /**
     * 查询出库书籍名称和数量
     * @return
     */
    @Override
    public List<Map<String, Object>> getckbook() {
        String sql = "select sum(x.count) value,bookname  from t_ckdetail x JOIN t_book b on x.bookid = b.bookid GROUP BY x.bookid";
        List<Map<String, Object>> list = JDBCUtil.queryMap(sql);
        if (list.size()>0){
            return list;
        }else {
            return null;
        }
    }
    /**
     * 查询出库书籍类型和数量
     * @return
     */
    @Override
    public List<Map<String, Object>> getcktype() {
        String sql = "select sum(x.count) value,typename  from t_ckdetail x JOIN t_book b on x.bookid = b.bookid JOIN t_type t on t.typeid = b.typeid GROUP BY t.typeid";
        List<Map<String, Object>> list = JDBCUtil.queryMap(sql);
        if (list.size()>0){
            return list;
        }else {
            return null;
        }
    }
    /**
     * 查询入库金额按月分组
     * @return
     */
    @Override
    public List<Map<String, Object>> getruku(String time) {
        StringBuilder sql = new StringBuilder("select year(rdate) year, month(rdate) month, sum(totalprice) price from t_ruku where status=1 ");
        if (time!=null && !"".equals(time)){
            sql.append(" and rdate >= '").append(time.substring(0,7)).append("'");
        }
        if (time!=null && !"".equals(time)){
            sql.append(" and rdate <= '").append(time.substring(10)).append("'");
        }
        sql.append(" group by year(rdate), month(rdate)");
        List<Map<String, Object>> list = JDBCUtil.queryMap(sql.toString());
        if (list.size()>0){
            return list;
        }else {
            return null;
        }
    }

    /**
     * 查询出库金额按月分组
     * @return
     */
    @Override
    public List<Map<String, Object>> getchuuku(String time) {
        StringBuilder sql = new StringBuilder("select year(cdate) year, month(cdate) month, sum(totalprice) price from t_ck where status=1 ");
        if (time!=null && !"".equals(time)){
            sql.append(" and cdate >= '").append(time.substring(0,7)).append("'");
        }
        if (time!=null && !"".equals(time)){
            sql.append(" and cdate <= '").append(time.substring(10)).append("'");
        }
        sql.append(" group by year(cdate), month(cdate)");
        List<Map<String, Object>> list = JDBCUtil.queryMap(sql.toString());
        if (list.size()>0){
            return list;
        }else {
            return null;
        }
    }
    /**
     * 查询订单金额按月分组
     * @return
     */
    @Override
    public List<Map<String, Object>> getdd(String time) {
        StringBuilder sql = new StringBuilder("select year(ddate) year, month(ddate) month, sum(totalprice) price from t_dd where 1=1 ");
        if (time!=null && !"".equals(time)){
            sql.append(" and ddate >= '").append(time.substring(0,7)).append("'");
        }
        if (time!=null && !"".equals(time)){
            sql.append(" and ddate <= '").append(time.substring(10)).append("'");
        }
        sql.append(" group by year(ddate), month(ddate)");
        List<Map<String, Object>> list = JDBCUtil.queryMap(sql.toString());
        if (list.size()>0){
            return list;
        }else {
            return null;
        }
    }
}
